﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using System.IO;

using CTEM.Models;
using CTEM.Controllers.Global;

namespace CTEM.Controllers.DAO
{
    class WorkSalaryDAO
    {
        public WorkSalaryDAO()
        {
            Transaction = null;
        }

        public SqlTransaction Transaction { get; set; }
        private SqlConnection _con;

        SqlConnection Connection
        {
            get
            {
                if (_con == null)
                    _con = new SqlConnection(DatabaseInfo.ConnectionString);
                if (_con.State == ConnectionState.Closed)
                {
                    _con.Open();
                }
                return _con;
            }
            set
            {
                _con = value;
            }
        }
        // LOAD toàn bộ wksalary board
        public List<WorkerSalaryDTO> LoadAllWks()
        {
            var cmd = new SqlCommand("[dbo].[WorkerSalary_GetAll]", Connection);

            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                List<WorkerSalaryDTO> listcons = new List<WorkerSalaryDTO>();
                while (reader.Read())
                {
                    WorkerSalaryDTO dto = new WorkerSalaryDTO
                    {
                        WorkersSalaryID = Convert.ToInt64(reader["WorkersSalaryID"]),
                        ConstructionID = Convert.ToInt64(reader["ConstructionID"]),
                        Name = Convert.ToString(reader["Name"]),
                        ConstructionName = Convert.ToString(reader["ConstructionName"]),
                        FromDate = Convert.ToDateTime(reader["FromDate"]),
                        ToDate = Convert.ToDateTime(reader["ToDate"]),
                        TotalSalary = Convert.ToInt64(reader["TotalSalary"]),
                        CreateDate = Convert.ToDateTime(reader["CreateDate"]),
                        Reason = Convert.ToString(reader["Reason"]),
                        Task = Convert.ToString(reader["Task"]),
                        CreatedBy = Convert.ToString(reader["CreatedBy"]),
                        UpdatedBy = Convert.ToString(reader["UpdatedBy"]),
                        Manager = Convert.ToString(reader["Manager"]),
                        OthersCost = (long)Convert.ToInt64(reader["OthersCost"]),
                        TotalCost = (long)Convert.ToInt64(reader["TotalCost"]),
                       
                        
                    
                    };

                    try
                    {
                        dto.LastUpdate = Convert.ToDateTime(reader["LastUpdate"]);
                    }
                    catch
                    { 
                      
                    }
                        
          
                 
                    listcons.Add(dto);
                }
                return listcons;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message, " Error SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                if (Transaction == null) Connection.Close();
            }
        }
        public List<WorkerSalaryDTO> search(WorkerSalaryDTO param)
        {
            var cmd = new SqlCommand("[dbo].[WorkerSalary_search]", Connection);

            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            if (param.ConstructionID > 0)
                cmd.Parameters.Add(new SqlParameter("@constructionID", param.ConstructionID));
            else
                cmd.Parameters.Add(new SqlParameter("@constructionID", DBNull.Value));
            if (!param.Name.Trim().Equals(""))
                cmd.Parameters.Add(new SqlParameter("@name","%"+ param.Name+"%"));
            else
                cmd.Parameters.Add(new SqlParameter("@name", DBNull.Value));
            cmd.Parameters.Add(new SqlParameter("@month", param.month));
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                List<WorkerSalaryDTO> listcons = new List<WorkerSalaryDTO>();
                while (reader.Read())
                {
                    WorkerSalaryDTO dto = new WorkerSalaryDTO
                    {
                        WorkersSalaryID = Convert.ToInt64(reader["WorkersSalaryID"]),
                        ConstructionID = Convert.ToInt64(reader["ConstructionID"]),
                        Name = Convert.ToString(reader["Name"]),
                        ConstructionName = Convert.ToString(reader["ConstructionName"]),
                        FromDate = Convert.ToDateTime(reader["FromDate"]),
                        ToDate = Convert.ToDateTime(reader["ToDate"]),
                        TotalSalary = Convert.ToInt64(reader["TotalSalary"]),
                        CreateDate = Convert.ToDateTime(reader["CreateDate"]),
                        OthersCost = Convert.ToInt64(reader["OthersCost"]),
                        Reason = Convert.ToString(reader["Reason"]),
                        TotalCost = Convert.ToInt64(reader["TotalCost"]),
                        Manager = Convert.ToString(reader["Manager"]),
                        ManagerName = Convert.ToString(reader["Username"]),
                        Task = Convert.ToString(reader["Task"]),
                        LastUpdate = Convert.ToDateTime(reader["LastUpdate"])
                    };
                   
                       
                
   
                    listcons.Add(dto);
                }
                
                return listcons;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message, "Error SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }

            finally
            {
                if (Transaction == null) Connection.Close();
            }
        }

        public WorkerSalaryDTO LoadWSByWorkerSalaryID(long workerSalaryID)
        {
            var cmd = new SqlCommand("[dbo].[WorkerSalary_GetByID]", Connection);

            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@WorkerSalaryID", workerSalaryID));
            
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
               
                while (reader.Read())
                {
                    WorkerSalaryDTO dto = new WorkerSalaryDTO();

                    
                    dto.WorkersSalaryID = (long)Convert.ToInt64(reader["WorkersSalaryID"]);
                    
                    dto.ConstructionID = (long)Convert.ToInt64(reader["ConstructionID"]);
                    dto.Name = Convert.ToString(reader["Name"]);
                    dto.FromDate = Convert.ToDateTime(reader["FromDate"]);
                    dto.ToDate = Convert.ToDateTime(reader["ToDate"]);
                   
                    dto.TotalSalary = (long)Convert.ToInt64(reader["TotalSalary"]);
                    dto.CreateDate = Convert.ToDateTime(reader["CreateDate"]);
                    dto.Reason = Convert.ToString(reader["Reason"]);
                    dto.Task = Convert.ToString(reader["Task"]);
                    dto.CreatedBy = Convert.ToString(reader["CreatedBy"]);
                    dto.UpdatedBy = Convert.ToString(reader["UpdatedBy"]);
                    dto.Manager = Convert.ToString(reader["Manager"]);
                    
                    dto.OthersCost = (long)Convert.ToInt64(reader["OthersCost"]);
                    
                    dto.TotalCost = (long)Convert.ToInt64(reader["TotalCost"]);

                    try
                    {
                        dto.LastUpdate = Convert.ToDateTime(reader["LastUpdate"]);
                    }
                    catch
                    { }
                
                    
                    return dto;
                }
                return null;
               
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message, " Error SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                if (Transaction == null) Connection.Close();
            }
        }

        public long getConID(long ID)
        {
            var cmd = new SqlCommand("[dbo].[WorkerSalary_GetConID]", Connection);

            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@WorkersSalaryID", ID));
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return reader[0] == DBNull.Value ? 0 : Convert.ToInt64(reader[0]);
                }
                return 0;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message, "Error SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }
            finally
            {
                if (Transaction == null) Connection.Close();
            }
        }


        public long getTotalByCon(long ID)
        {
            var cmd = new SqlCommand("[dbo].[WorkerSalary_GetTotal]", Connection);

            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@ConstructionID", ID));
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return reader[0] == DBNull.Value ? 0 : Convert.ToInt64(reader[0]);
                }
                return 0;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message, "Error SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }
            finally
            {
                if (Transaction == null) Connection.Close();
            }
        }

        public long CreateWks(WorkerSalaryDTO dto)
        {
            var cmd = new SqlCommand("[dbo].[WorkerSalary_Create]", Connection) { CommandType = CommandType.StoredProcedure };
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            try
            {
                cmd.Parameters.Add(new SqlParameter("@constructionID", dto.ConstructionID));
                cmd.Parameters.Add(new SqlParameter("@Manager", dto.Manager));
                cmd.Parameters.Add(new SqlParameter("@OthersCost", dto.OthersCost));
                cmd.Parameters.Add(new SqlParameter("@TotalCost", dto.TotalCost));
                cmd.Parameters.Add(new SqlParameter("@name", dto.Name));                
                cmd.Parameters.Add(new SqlParameter("@reason", dto.Reason));
                cmd.Parameters.Add(new SqlParameter("@Task", dto.Task));
                cmd.Parameters.Add(new SqlParameter("@fromDate", dto.FromDate));
                cmd.Parameters.Add(new SqlParameter("@toDate", dto.ToDate));
                cmd.Parameters.Add(new SqlParameter("@totalSalary", dto.TotalSalary));
                cmd.Parameters.Add(new SqlParameter("@createdBy", Global.Global.CurrentUser.Username));

              
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return reader[0] == DBNull.Value ? 0 : Convert.ToInt64(reader[0]);
                }
                return 0;
            
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message);
                return 0;
            }
            finally
            {
                if (Transaction == null)
                    Connection.Close();
            }
        }

        public bool UpdateWks(WorkerSalaryDTO dto)
        {
            var cmd = new SqlCommand("[dbo].[WorkerSalary_Update]", Connection) { CommandType = CommandType.StoredProcedure };
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            try
            {
                cmd.Parameters.Add(new SqlParameter("@workersSalaryID", dto.WorkersSalaryID));
                cmd.Parameters.Add(new SqlParameter("@constructionID", dto.ConstructionID));
                cmd.Parameters.Add(new SqlParameter("@name", dto.Name));
                cmd.Parameters.Add(new SqlParameter("@fromDate", dto.FromDate));
                cmd.Parameters.Add(new SqlParameter("@Task", dto.Task));
                cmd.Parameters.Add(new SqlParameter("@toDate", dto.ToDate));
                cmd.Parameters.Add(new SqlParameter("@totalSalary", dto.TotalSalary));
                cmd.Parameters.Add(new SqlParameter("@updatedBy", Global.Global.CurrentUser.Username));
                cmd.Parameters.Add(new SqlParameter("@Manager", dto.Manager));
                cmd.Parameters.Add(new SqlParameter("@OthersCost", dto.OthersCost));
                cmd.Parameters.Add(new SqlParameter("@reason", dto.Reason));
                cmd.Parameters.Add(new SqlParameter("@TotalCost", dto.TotalCost));
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message);
                return false;
            }
            finally
            {
                if (Transaction == null)
                    Connection.Close();
            }
        }
        public bool UpdateTotalSalary(long id, long ConstructionID)
        {
            var cmd = new SqlCommand("[dbo].[WorkerSalary_UpdateTotalSalary]", Connection) { CommandType = CommandType.StoredProcedure };
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            try
            {
                cmd.Parameters.Add(new SqlParameter("@workersSalaryID", id));
                cmd.Parameters.Add(new SqlParameter("@ConstructionID", ConstructionID));
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message);
                return false;
            }
            finally
            {
                if (Transaction == null)
                    Connection.Close();
            }
        }
        public bool delete(long id)
        {
            var cmd = new SqlCommand("[dbo].[WorkersSalary_Delete]", Connection) { CommandType = CommandType.StoredProcedure };
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            try
            {
                cmd.Parameters.Add(new SqlParameter("@workersSalaryID", id));
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message);
                return false;
            }
            finally
            {
                if (Transaction == null)
                    Connection.Close();
            }
        }

    }
}
